"""
Case Type   : security-Separation_rights
Case Name   : 三权分立后系统管理员对表空间有分配的权限
Create At   : 2020/9/17
Owner       : opentestcase003
Description :
    1.设置相关参数
    2.初始用户创建测试数据：CREATE USER u_name1 WITH SYSADMIN password '$PASSWORD';
    CREATE USER u_name2 WITH password '$PASSWORD';
    CREATE TABLESPACE tbs_name RELATIVE LOCATION 'tablespace/tablespace_2';
    3.用户执行授权：GRANT CREATE ON TABLESPACE tbs_name TO u_name2;
    4.清理环境
Expect      :
    1.参数设置成功
    2.测试数据创建成功
    3.授权成功
    4.环境清理完成
History     :
    modified by opentestcase003 2021/08/31修改密码描述
    modified by opentestcase002 2022/08/12优化用例，适配新代码
    modified by l1azzzy 2025/5/17 sysadmin创建表空间，并分配权限
"""

import os
import unittest
from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Policy(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.common = Common()
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.userNode = Node('PrimaryDbUser')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.Constant = Constant()
        self.tbs_name = 'tbs_separation_case0007'
        self.u_name1 = 'u_separation_case0007_01'
        self.u_name2 = 't_separation_case0007_02'
        self.tabspace_path = os.path.join('tablespace', 'tablespace_2')
        self.assert_msg = f'GRANT'

    def test_policy(self):
        text = '-----step1:设置相关参数,expect:成功-----'
        self.log.info(text)
        excute_cmd0 = f'source {self.DB_ENV_PATH};' \
            f'gs_guc set -D {macro.DB_INSTANCE_PATH}' \
            f' -c "enableSeparationOfDuty=on";'
        msg0 = self.userNode.sh(excute_cmd0).result()
        self.log.info(msg0)
        result = self.sh_primy.restart_db_cluster()
        self.log.info(result)
        result = self.sh_primy.get_db_cluster_status('detail')
        self.log.info(result)
        self.new_value = self.common.show_param("enableSeparationOfDuty")
        self.log.info(self.new_value)
        self.assertTrue("Degraded" in result or "Normal" in result,
                        '执行失败:' + text)
        self.assertEqual('on', self.new_value, '执行失败:' + text)

        text = '-----step2:初始用户创建测试数据,expect:成功-----'
        self.log.info(text)
        sql_cmd1 = f'create user {self.u_name1} with sysadmin password ' \
            f'\'{macro.COMMON_PASSWD}\';' \
            f'create user {self.u_name2} with ' \
            f'password \'{macro.COMMON_PASSWD}\';' 
        msg1 = self.sh_primy.execut_db_sql(sql_cmd1)
        self.log.info(msg1)
        self.assertEqual(2, msg1.count('CREATE ROLE'), '执行失败:' + text)

        text = '-----step3:用户执行授权操作,expect:授权失败-----'
        self.log.info(text)
        sql_cmd2 = f'create tablespace {self.tbs_name} relative location ' \
            f'\'{self.tabspace_path}\';'
        excute_cmd2 = f'source {self.DB_ENV_PATH};' \
            f'gsql -d {self.userNode.db_name} -p'\
            f'{self.userNode.db_port} -U {self.u_name1} -W'\
            f'\'{macro.COMMON_PASSWD}\' -c "{sql_cmd2}"'
        self.log.info(excute_cmd2)
        msg2 = self.userNode.sh(excute_cmd2).result()
        self.log.info(msg2)
        self.assertIn(self.Constant.TABLESPCE_CREATE_SUCCESS, msg2,
                      '执行失败:' + text)
        sql_cmd3 = f'grant create on tablespace ' \
            f'{self.tbs_name} to {self.u_name2};'
        excute_cmd3 = f'source {self.DB_ENV_PATH};' \
            f'gsql -d {self.userNode.db_name} -p ' \
            f'{self.userNode.db_port} -U {self.u_name1} -W ' \
            f'\'{macro.COMMON_PASSWD}\' -c "{sql_cmd3}"'
        self.log.info(excute_cmd3)
        msg3 = self.userNode.sh(excute_cmd3).result()
        self.log.info(msg3)
        self.assertIn(self.assert_msg, msg3, '执行失败:' + text)

    def tearDown(self):
        text = '-----step4:清理环境,expect:成功-----'
        self.log.info(text)
        self.log.info('---------恢复参数默认值---------')
        excute_cmd0 = f'source {self.DB_ENV_PATH};' \
            f'gs_guc set -D {macro.DB_INSTANCE_PATH} ' \
            f'-c "enableSeparationOfDuty=off";'
        msg3 = self.userNode.sh(excute_cmd0).result()
        self.log.info(msg3)
        self.log.info('--------重启数据库---------')
        restart_msg = self.sh_primy.restart_db_cluster()
        self.log.info(restart_msg)
        status = self.sh_primy.get_db_cluster_status()
        self.default_value = self.common.show_param("enableSeparationOfDuty")
        self.log.info(self.default_value)
        self.log.info('--------清理创建的测试数据---------')
        sql_cmd1 = f'drop tablespace if exists {self.tbs_name};' \
            f'drop user {self.u_name2};' \
            f'drop user {self.u_name1};'
        msg4 = self.sh_primy.execut_db_sql(sql_cmd1)
        self.log.info(msg4)
        self.assertTrue("Degraded" in status or "Normal" in status,
                        '执行失败:' + text)
        self.assertEqual('off', self.default_value, '执行失败:' + text)
        self.assertEqual(2, msg4.count('DROP ROLE'), '执行失败:' + text)
        self.assertIn(self.Constant.TABLESPCE_DROP_SUCCESS, msg4,
                      '执行失败:' + text)
        text = f'-----{os.path.basename(__file__)} end-----'
        self.log.info(text)
